package cc.messcat.service.fupin;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cc.messcat.common.model.Pager;
import cc.messcat.common.service.BaseServiceImpl;
import cc.messcat.dao.area.AreaDao;
import cc.messcat.dao.fupin.DequeueDao;
import cc.messcat.dao.helpTeam.HelpTeamDao;
import cc.messcat.entity.area.Area;
import cc.messcat.entity.fupin.Dequeue;
import cc.messcat.entity.fupin.PoorVillage;
import cc.messcat.entity.fupin.VisitRecd;
import cc.messcat.entity.system.McUser;
import cc.messcat.util.ExcelUtil;

@Service
public class DequeueServiceImpl extends BaseServiceImpl<Dequeue> implements DequeueService {

	@Autowired
    private DequeueDao dequeueDao;
	
	@Autowired
    private AreaDao areaDao;
	
	@Override
	public Pager<Dequeue> getPager(Dequeue dequeue,String nature,Integer pageNo, Integer pageSize,McUser user,String pid,String cry,String name) {
		Map map = new HashMap<>();
	    StringBuffer hql = new StringBuffer();
	    hql.append("FROM Dequeue  WHERE 1=1 ");
	    //省
        if (user.getProvince()!= null && !"".equals(user.getProvince())) {
            String province = user.getProvince().replaceAll(" ", "");
            hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE province LIKE '%" + province + "%') ");
        }
        //市
        if (user.getCity()!= null && !"".equals(user.getCity())) {
            String city = user.getCity() .replaceAll(" ", "");
            hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE city LIKE '%" + city + "%')");
        }
        //县
        if (user.getCty()!= null && !"".equals(user.getCty())) {
            String cty = user.getCty().replaceAll(" ", "");
            hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cty LIKE '%" + cty + "%')");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(cry != null && !"".equals(cry)){
        		   Area area = areaDao.getAreaByCode(cry);
                   hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cry LIKE '%" + area.getArea() + "%')");
                   
        	   }else{
        		   Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
               	   String cry2 = area.getArea(); 
                   hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cry LIKE '%" + cry2 + "%')");
        	   }            	
        }else{
        	 if(cry != null && !"".equals(cry)){
      		      Area area = areaDao.getAreaByCode(cry);
                 hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cry LIKE '%" + area.getArea() + "%')");
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(name!= null && !"".equals(name)){
        		Area area = areaDao.getAreaByCode(name);
                hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE name LIKE '%" + area.getArea() + "%')");
        	}else{
        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
            	String name2 = area.getArea(); 
            	hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE name LIKE '%" + name2 + "%')");
        	}       	
        }else{
        	if(name != null && !"".equals(name)){
        		Area area = areaDao.getAreaByCode(name);
                hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE name LIKE '%" + area.getArea() + "%')");
        	}
        }
	    //业务年度
        if (StringUtils.isNotBlank(dequeue.getAnnual())) {
            hql.append(" AND annual = '" + dequeue.getAnnual() + "'");
        }
        //村属性
        if (nature!= null && !"".equals(nature)) {
            nature = nature.replaceAll(" ", "");
            hql.append(" AND originPoorVillage.nature = '" + nature + "'");
        }
        //变更类型
        if (dequeue.getChanges()!= null && !"".equals(dequeue.getChanges())) {
            String changes = dequeue.getChanges();
            hql.append(" AND changes = '" + changes + "'");
        }
        if (pid!=null && !"".equals(pid)) {
       	//变更后的贫困村
       	if(pid.equals("1")){
               hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage)");
           }
        //变更前的贫困村
        if(pid.equals("2")){
           	hql.append(" AND poorVillageHistory.originId in (SELECT id FROM PoorVillage)");
               }
		}
		return this.dequeueDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
	}

	@Override
	public HSSFWorkbook exportVisitRecd(Dequeue dequeue,String nature,McUser user,String pid,String cry,String name) {
		String[] cellName = new String[]
				{"序号","省","市","县","乡镇","村","村负责人","村办公电话","村属性","变更类型"};
		String[] field = new String[] 
				{"poorVillageHistory.province","poorVillageHistory.city","poorVillageHistory.cty","poorVillageHistory.cry","poorVillageHistory.name","poorVillageHistory.fpeople","poorVillageHistory.contact","poorVillageHistory.nature","changes"};
		
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();

		
		do {
			sheetCount++;
			pager  = getPager(dequeue,nature,sheetCount, 5000,user,pid,cry,name);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		for(Object e :pager.getResultList()) {
			if (((Dequeue)e).getChanges().equals("0")) {
				((Dequeue)e).setChanges("贫困村出列");
			}
			if (((Dequeue)e).getChanges().equals("1")) {
				((Dequeue)e).setChanges("贫困村取消出列");
			}
			if (((Dequeue)e).getChanges().equals("2")) {
				((Dequeue)e).setChanges("贫困村新增");
			}
			if (((Dequeue)e).getChanges().equals("3")) {
				((Dequeue)e).setChanges("贫困村信息变更");
			}
			if (((Dequeue)e).getChanges().equals("4")) {
				((Dequeue)e).setChanges("贫困村删除");
			}
		}

		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		/*VisitRecd  a  =(VisitRecd)pager.getResultList();
		a.getPoorFamily().getFamily().get(0).getName();*/
//		familyDao.queryList("select *from ");
		//名字为贫困户户主姓名_走访记录.xls
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困村变更" + i);
			sheet.setDefaultColumnWidth(22);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

			ExcelUtil.addTitle(sheet,cellName,"贫困村变更",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	
	
	}

	@Override
	public List<Dequeue> Dequeue(Long id) {
		 Map map = new HashMap<>();
        String hql = "from Dequeue where id= :id ";
        map.put("id", id);
        return this.dequeueDao.queryList(hql, map);
	}

	@Override
	public Pager<cc.messcat.entity.fupin.Dequeue> getPagertwo(cc.messcat.entity.fupin.Dequeue dequeue, String nature,Integer pageNo, Integer pageSize, McUser user, String pid, String cry, String name) {
		Map map = new HashMap<>();
	    StringBuffer hql = new StringBuffer();
	    hql.append("FROM Dequeue  WHERE 1=1 ");
	    //省
        if (user.getProvince()!= null && !"".equals(user.getProvince())) {
            String province = user.getProvince().replaceAll(" ", "");
            hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE province LIKE '%" + province + "%') ");
        }
        //市
        if (user.getCity()!= null && !"".equals(user.getCity())) {
            String city = user.getCity() .replaceAll(" ", "");
            hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE city LIKE '%" + city + "%')");
        }
        //县
        if (user.getCty()!= null && !"".equals(user.getCty())) {
            String cty = user.getCty().replaceAll(" ", "");
            hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cty LIKE '%" + cty + "%')");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(cry != null && !"".equals(cry)){
        		   Area area = areaDao.getAreaByCode(cry);
                   hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cry LIKE '%" + area.getArea() + "%')");
                   
        	   }else{
        		   Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
               	   String cry2 = area.getArea(); 
                   hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cry LIKE '%" + cry2 + "%')");
        	   }            	
        }else{
        	 if(cry != null && !"".equals(cry)){
      		      Area area = areaDao.getAreaByCode(cry);
                 hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE cry LIKE '%" + area.getArea() + "%')");
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(name!= null && !"".equals(name)){
        		Area area = areaDao.getAreaByCode(name);
                hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE name LIKE '%" + area.getArea() + "%')");
        	}else{
        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
            	String name2 = area.getArea(); 
            	hql.append("  AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE name LIKE '%" + name2 + "%')");
        	}       	
        }else{
        	if(name != null && !"".equals(name)){
        		Area area = areaDao.getAreaByCode(name);
                hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage WHERE name LIKE '%" + area.getArea() + "%')");
        	}
        }
	    //业务年度
        if (StringUtils.isNotBlank(dequeue.getAnnual())) {
            hql.append(" AND annual = '" + dequeue.getAnnual() + "'");
        }
        //村属性
        if (nature!= null && !"".equals(nature)) {
            nature = nature.replaceAll(" ", "");
            hql.append(" AND originPoorVillage.nature = '" + nature + "'");
        }
        //变更类型
        if (dequeue.getChanges()!= null && !"".equals(dequeue.getChanges())) {
            String changes = dequeue.getChanges();
            hql.append(" AND changes = '" + changes + "'");
        }
        if (pid!=null && !"".equals(pid)) {
       	//变更后的贫困村
       	if(pid.equals("1")){
               hql.append(" AND originPoorVillage.id in (SELECT id FROM PoorVillage)");
           }
        //变更前的贫困村
        if(pid.equals("2")){
           	hql.append(" AND poorVillageHistory.originId in (SELECT id FROM PoorVillage)");
               }
		}
		return this.dequeueDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
	}
	
	

}
